package com.fingard.dsp.bank.dbobj.receipt;

import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Map;

import com.fingard.FGBiz;
import com.fingard.constant.Format;
import com.fingard.db.JDBCHelper;
import com.fingard.dsp.bank.dbobj.unique.UniqueIdHelper;
import com.fingard.text.StringHelper;
import com.fingard.xml.XElement;

public class ReceiptDbHelper {

	/**按busRef、receiptNo、askNumber中的一个条件取回单映射数据*/
	public static ReceiptMapping getRcptMpByOne(Connection pConn, ReceiptMapping pRcptMp) throws Exception{
		if(StringHelper.isNullOrEmpty(pRcptMp.ownAct)){
			throw new Exception("取回单映射数据时，ownAct条件不能为空");
		}
		String keyValue = pRcptMp.getKeyValue();
		Map<String,Object> rcptMap = null;
		if(StringHelper.hasAnyChar(keyValue)){
			String sql = "select * from RECEIPTMAPPING where "+pRcptMp.keyName.toUpperCase()+"=? and OWNACT=?";
			rcptMap = JDBCHelper.execQuerySingle(pConn, sql, new Object[]{keyValue, pRcptMp.ownAct});
			if(rcptMap!= null && rcptMap.size() > 0){
				ReceiptMapping retMp = new ReceiptMapping();
				retMp.fromMap(rcptMap);
				return retMp;
			}
		}else{
			throw new Exception("取回单映射数据时，"+pRcptMp.keyName+"条件不能为空");
		}
		
		return null;
	}
	
	/**根据AskNumber、BusRef、ReceiptNo加或者不加ownAct，任意查找回单映射数据*/
	public static ReceiptMapping getRcptMpByAny(ReceiptMapping pRcptMp){
		if(pRcptMp == null){
			return null;
		}
		Connection conn = null;
		try{
			conn = FGBiz.getLocalDbConn();
			
			/*
			List<Map<String, Object>> mm = JDBCHelper.execQuery(conn, "select * from RECEIPTMAPPING", null);
			for(Map<String, Object> eachM : mm){
				for(String eachK : eachM.keySet()){
					System.out.print(eachK+":"+eachM.get(eachK)+"\t");
				}
				System.out.println("");
			}
			*/
			
			ArrayList<String> arrSql = new ArrayList<String>();
			ArrayList<Object[]> arrParams = new ArrayList<Object[]>();
			
			String[] busVals = StringHelper.joinNotEmpty(",", pRcptMp.receiptNo,pRcptMp.busRef).split(",");
			
			if(StringHelper.hasAnyChar(pRcptMp.ownAct)){
				if(StringHelper.hasAnyChar(pRcptMp.receiptNo)){
					arrSql.add("select * from RECEIPTMAPPING where RECEIPTNO=? and OWNACT=?");
					arrParams.add(new Object[]{pRcptMp.receiptNo, pRcptMp.ownAct});
				}
				
				for(int i=0;i<busVals.length;i++){
					if(StringHelper.hasAnyChar(busVals[i])){
						arrSql.add("select * from RECEIPTMAPPING where BUSREF=? and OWNACT=?");
						arrParams.add(new Object[]{busVals[i], pRcptMp.ownAct});
					}
				}
				if(StringHelper.hasAnyChar(pRcptMp.askNumber)){
					arrSql.add("select * from RECEIPTMAPPING where ASKNUMBER=? and OWNACT=?");
					arrParams.add(new Object[]{pRcptMp.askNumber, pRcptMp.ownAct});
				}
			}
			if(StringHelper.isNullOrEmpty(pRcptMp.ownAct)){
				if(StringHelper.hasAnyChar(pRcptMp.receiptNo)){
					arrSql.add("select * from RECEIPTMAPPING where RECEIPTNO=?");
					arrParams.add(new Object[]{pRcptMp.receiptNo});
				}
				
				for(int i=0;i<busVals.length;i++){
					if(StringHelper.hasAnyChar(busVals[i])){
						arrSql.add("select * from RECEIPTMAPPING where BUSREF=?");
						arrParams.add(new Object[]{busVals[i]});
					}
				}
				
				
				if(StringHelper.hasAnyChar(pRcptMp.askNumber)){
					arrSql.add("select * from RECEIPTMAPPING where ASKNUMBER=?");
					arrParams.add(new Object[]{pRcptMp.askNumber});
				}	
			}
			
			
			for(int i=0;i<arrSql.size();i++){
				String sql = arrSql.get(i);
				Object[] param = arrParams.get(i);
				Map<String,Object> rcptMap = JDBCHelper.execQuerySingle(conn, sql, param);
				if(rcptMap!= null && rcptMap.size() > 0){
					ReceiptMapping retMp = new ReceiptMapping();
					retMp.fromMap(rcptMap);
					return retMp;
				}
			}
			
			
		}catch(Exception ex){
			FGBiz.limitMsg.exception("getRcptMpByAskNumber", ex);
		}finally{
			if(conn != null){
				try{
				conn.close();
				}catch(Exception ex){
					FGBiz.limitMsg.exception("getRcptMpByAny", ex);
				}
			}
		}
		return null;
	}
	
	/**根据AskNumber查找回单映射数据*/
	public static ReceiptMapping getRcptMpByAskNumber(String pAsk){
		if(StringHelper.isNullOrEmpty(pAsk)){
			return null;
		}
		Connection conn = null;
		try{
			conn = FGBiz.getLocalDbConn();
			String sql = "select * from RECEIPTMAPPING where ASKNUMBER=?";
			Map<String,Object> rcptMap = JDBCHelper.execQuerySingle(conn, sql, new Object[]{pAsk});
			if(rcptMap!= null && rcptMap.size() > 0){
				ReceiptMapping retMp = new ReceiptMapping();
				retMp.fromMap(rcptMap);
				return retMp;
			}
		}catch(Exception ex){
			FGBiz.limitMsg.exception("getRcptMpByAskNumber", ex);
		}finally{
			if(conn != null){
				try{
				conn.close();
				}catch(Exception ex){
					FGBiz.limitMsg.exception("getRcptMpByAskNumber", ex);
				}
			}
		}
		return null;
	}
	
	public static boolean saveReceiptMappingByXe(XElement xe) throws Exception{
		ReceiptMapping mp = new ReceiptMapping();
		mp.fromXml(xe);
		Connection conn = null;
		try{
			conn = FGBiz.getLocalDbConn();
			return saveReceiptMapping(conn, mp);
		}catch(Exception ex){
			throw(ex);
		}finally{
			if(conn != null){
				try{
				conn.close();
				}catch(Exception ex){
					ex.printStackTrace();
				}
			}
		}
	}
	
	public static boolean existRcptMpByOne(Connection pConn, ReceiptMapping pRcptMp) throws Exception{
		String keyValue = pRcptMp.getKeyValue();
		String sql = "select URID from RECEIPTMAPPING where "+pRcptMp.keyName.toUpperCase()+"=? and OWNACT=?";
		Map<String,Object> rcptMap = JDBCHelper.execQuerySingle(pConn, sql, new Object[]{keyValue, pRcptMp.ownAct});
		if(rcptMap!= null && rcptMap.size() > 0){
			return true;
		}
		return false;
	}
	
	public static boolean saveReceiptMapping(Connection pConn, ReceiptMapping pRcptMp) throws Exception{
		if(!existRcptMpByOne(pConn,pRcptMp)){
			long urid = UniqueIdHelper.getUniqueId("RECEIPTMAPPING");
			String createdOn = new SimpleDateFormat(Format.DateIsoFormatStr).format(new Date());
			String sql = "INSERT INTO RECEIPTMAPPING(URID,ASKNUMBER,BUSREF,RECEIPTNO,BUSDATE,OWNACT,REQBATCHNO,CUSTOMFILEID,BANK,MONEYWAY,AMOUNT,RECEIPTFILE,SRCFILE,CREATEDON) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			int rowsAff = JDBCHelper.update(pConn, sql, new Object[]{urid, 
					pRcptMp.askNumber,
					pRcptMp.busRef,
					pRcptMp.receiptNo,
					pRcptMp.busDate,
					pRcptMp.ownAct,
					pRcptMp.reqBatchNo,
					pRcptMp.customFileId,
					pRcptMp.bank,
					pRcptMp.moneyWay,
					pRcptMp.amount,
					pRcptMp.receiptFile,
					pRcptMp.srcFile,
					createdOn});
			
			if(rowsAff>0){
	        	return true;
	        }
			return false;
		}else{
			return true;//已存在，直接返回true
		}
	}
	
	public static String getCustomFileId(Connection pConn, ReceiptMapping pRcptMp) throws Exception{
		String sql = "SELECT * FROM RECEIPTCUSTOMFILE WHERE FILEKEY=? AND OWNACT=? AND BUSDATE=?";
		Map<String,Object> mp = JDBCHelper.execQuerySingle(pConn, sql, new Object[]{pRcptMp.getKeyValue(), pRcptMp.ownAct,pRcptMp.busDate});
		if(mp!= null && mp.size() > 0){
			return String.valueOf(mp.get("CUSTOMFILEID"));
		}
		return null;
	}
	
	public static boolean saveCustomFileId(Connection pConn, ReceiptMapping pRcptMp) throws Exception{
		long urid = UniqueIdHelper.getUniqueId("RECEIPTCUSTOMFILE");
		String createdOn = new SimpleDateFormat(Format.DateIsoFormatStr).format(new Date());
		String sql = "insert into RECEIPTCUSTOMFILE(URID,fileKey,busdate,ownact,customFileId,createdOn) values(?,?,?,?,?,?)";
		int rowsAff = JDBCHelper.update(pConn, sql, new Object[]{urid, pRcptMp.getKeyValue(),pRcptMp.busDate, pRcptMp.ownAct,pRcptMp.customFileId,createdOn});
		
		if(rowsAff>0){
        	return true;
        }
		return false;
	}
}
